package device;


import javax.servlet.*;
import javax.servlet.http.*;

import Utils.JDBCTemp;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;


import static java.lang.System.out;


public class device extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("执行了doGet");
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("执行了doPost");
    }

    protected void service(HttpServletRequest request, HttpServletResponse response) throws  IOException {
        System.out.println("执行了service");
        try {
            processQueryFile(request, response);
        } catch (JSONException | SQLException e) {
            e.printStackTrace();
        }
    }

    protected void processQueryFile(HttpServletRequest request, HttpServletResponse response) throws IOException, JSONException, SQLException {

        response.setCharacterEncoding("utf-8");
        response.setHeader("Cache-Control", "no-cache");
        request.setCharacterEncoding("UTF-8");
        String timeFrom = (new SimpleDateFormat("yyyy-MM-dd 00:00:00")).format(new Date());
        String timeTo = (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date());
        int gpsTotal=0;
        double gpsSpeed=0;
        double gpsMoney = 0;
        int gpsTotalChengDu=0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException classnotfoundexception) {
            classnotfoundexception.printStackTrace();
        }
        out.println("加载了JDBC驱动");

        try {
            Connection conn = DriverManager.getConnection("jdbc:mysql://www.ylxteach.net:3366/ylxdb?user=Administrator&password=XWClassroom20202023&userUnicode=true&characterEncoding=UTF-8");
            Statement statement = conn.createStatement();
            out.println("已经连接上数据库！");
            ResultSet gps = statement.executeQuery("SELECT count(*)  FROM gps_history where GPSTime BETWEEN '"+timeFrom+"' and '"+timeTo+"'");
            while (gps.next()) {
                gpsTotal = gps.getInt("count(*)");
            }
            ResultSet speed = statement.executeQuery("select avg(Speed) from gps_history where GPSTime BETWEEN'"+timeFrom+"' and '"+timeTo+"'"+"and Speed != 0");
            while (speed.next()) {
                gpsSpeed = speed.getDouble("avg(Speed)");
            }
            ResultSet money = statement.executeQuery("SELECT SUM(((Speed*10/36)*TIMESTAMPDIFF(SECOND,GPSTime,RecvTime)/1000)*0.6) 油耗 FROM gps_history where GPSTime BETWEEN'"+timeFrom+"' and '"+timeTo+"'"+"and Speed != 0");
            while (money.next()) {
                gpsMoney = money.getDouble("油耗");
            }
            ResultSet gpsChengDu = statement.executeQuery("SELECT count(*) FROM gps_history where GPSTime BETWEEN '"+timeFrom+"' and '"+timeTo+"'and Speed != 0 and location like '%成都%'");
            while (gpsChengDu.next()) {
                gpsTotalChengDu = gpsChengDu.getInt("count(*)");
            }
            statement.close();
            conn.close();
        } catch (SQLException sqlexception) {
            sqlexception.printStackTrace();
            out.println("查询失败");
        }

        JSONObject json = new JSONObject();
        json.put("gps_total", gpsTotal);
        json.put("gps_speed",gpsSpeed);
        json.put("gps_money",gpsMoney);
        json.put("gps_total_chengdu",gpsTotalChengDu);
        json.put("result_msg", "ok");
        json.put("result_code", 0);
        out.println("最后构造得到的json是:" + json);
        response.setContentType("text/html; charset=UTF-8");
        try {
            response.getWriter().print(json);
            response.getWriter().flush();
            response.getWriter().close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        out.println("返回结果给调用页面了。");
    }
}
